﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NIKE_Project.DAL;

namespace NIKE_Project.BLL
{
    public class GoodsBrowseServer
    {
        //引用DBHelper类
        DBHelper db = new DBHelper();
        DataSet ds;
        //商品浏览，单击显示选项卡
        public DataSet Sqldtl1Method()
        {
            //一级分类下拉框
            string Sqldt1 = string.Format("select t1.TypeName from Type t1,Type t2 where t1.TypeID=t2.TypeID and t1.TypeID in('1','2','3');");
            ds = db.getDataSet(Sqldt1);
            return ds;
        }
   
        public DataSet Sqldtl2Method()
        {
            //二级分类下拉框
            string Sqldt12 = string.Format("select t2.TypeName,t2.ParentID from Type t1,Type t2 where t1.TypeID=t2.TypeID and t2.ParentID in('1','2','3');");
            //string Sqldt12 = string.Format("select TypeName from Type where ParentID='{0}'", Type);
            ds = db.getDataSet(Sqldt12);
            return ds;
        }

        public DataSet DataGridViewMethod()
        {
            //加载DataGridview 1数据
            string Sqldd1 = string.Format("select * from Goods,Type where Goods.TypeID=Type.TypeID order by StockDate desc;");
            DataSet ds3 = db.getDataSet(Sqldd1);
            return ds3;
        }

        public DataSet SalesIDMethod(string i)
        {

            string BarCode = string.Format("select SalesID from Sales where ReceiptsCode='{0}'",i);
            DataSet ds = db.getDataSet(BarCode);
            return ds;
        }
        public DataSet SalesIDSumMethod(string SalesID)
        {
            string Sales = string.Format(@"select sum((SalesDetail.AloneAmount-Goods.StorePrice)*SalesDetail.Quantity) 
                                             from SalesDetail,Goods 
                                             where SalesDetail.GoodsID=Goods.GoodsID and SalesID='{0}'", SalesID);
            DataSet ds1 = db.getDataSet(Sales);
            return ds1;
        }

        //多条件组合查询商品函数
        public DataSet QueryGoods(string goodsCode, string goodsName, string typeName, DateTime startTime, DateTime endTime, string combooBox2, string combooBox6)
        {
            //定义查询所有商品的SQL语句
            string SqlStr = "select GoodsID,BarCode,GoodsName,Type.TypeName,StorePrice,SalePrice,Discount,StockNum,StockDate from Goods,Type where Goods.TypeID=Type.TypeID";
            //DataSet ds = db.getDataSet(SqlStr);
            //根据用户的选择，拼接不同的条件                   
            if (goodsCode != "")
            {
                //货号/条形码
                //SqlStr = string.Format("select * from Goods,Type where Goods.TypeID=Type.TypeID and BarCode like '{0}%';", goodsCode);
                SqlStr += " and BarCode like '" + goodsCode + "%'";
            }
            if (goodsName != "")
            {
                //商品名称
                SqlStr += " and GoodsName like '%" + goodsName + "%'";
            }
            if (typeName != "全部")
            {
                //二级分类
                //用户选择了二级分类，按二级分类查询
                SqlStr += " and TypeName = '" + typeName + "'";
            }
            else if (combooBox2 != "全部")
            {
                //一级分类
                string TypeName1 = combooBox2;
                //根据一级分类名称查询TypeID
                string Typeid = string.Format("select TypeID from Type where TypeName='{0}' ", TypeName1);
                DataSet dsId = db.getDataSet(Typeid);
                string Tpid = dsId.Tables["nike"].Rows[0]["TypeID"].ToString();
                //根据一级分类的TypeID用ParentID查询所有对应的商品
                //SqlStr = string.Format("select * from Goods,Type where Goods.TypeID=Type.TypeID and ParentID = '{0}';", Tpid);
                SqlStr += " and ParentID = '" + Tpid + "'";
            }
            if (combooBox6 != "全部")
            {
                //入库时间
                //SqlStr = string.Format("select * from Goods,Type where Goods.TypeID=Type.TypeID and StockDate between'{0}' and '{1}';", startTime.ToString("yyyy-MM-dd- 00:00:00"), endTime.ToString("yyyy-MM-dd 23:59:59"));
                //SqlStr += "and StockDate between'" + startTime.ToString("yyyy-MM-dd- 00:00:00") + "' and '" + endTime.ToString("yyyy-MM-dd 23:59:59") + "'";
                //按入库时间降序排序
                SqlStr += "and StockDate between'" + startTime + "' and '" + endTime + "'";
            }
            //根据入库时间降序排列
            SqlStr += " order by StockDate desc";
            //执行SQL语句
            DataSet ds1 = db.getDataSet(SqlStr);
            return ds1;

        }

        //删除一行数据
        public int deleteMethod(string id)
        {
            string Sqlinsert = string.Format("delete from Goods where GoodsID = '{0}'", id);
            int rows = db.zsg(Sqlinsert);
            return rows;
        }

        //刷新数据
        public DataSet refreshMethod()
        {
            string Sqldv4 = string.Format("select GoodsID,BarCode,TypeID,GoodsName,StorePrice,SalePrice,Discount,StockNum,StockDate from Goods;");
            ds = db.getDataSet(Sqldv4);
            return ds;
        }
    }
}
